package top.liuzhenyu.study.javawebjdbc.dao;

import top.liuzhenyu.study.javawebjdbc.entity.News;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDAO {
    public static int insert(News news) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "insert into news(title, body) values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, news.getTitle());
        ps.setString(2, news.getBody());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
    public static List<News> query() throws SQLException {
        List<News> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        String sql = "select * from news";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            News news = new News();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
            news.setBody(rs.getString("body"));
            news.setCreated(rs.getTimestamp("created"));
            list.add(news);
        }
        conn.close();
        return list;
    }

    public static int delete(Integer id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql = "delete from news where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }
}
